package com.yuda.afterexamscore.controller;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.ZipUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.yuda.afterexamscore.domain.SpartaSubject;
import com.yuda.afterexamscore.service.IImportScoreExcelService;
import com.yuda.afterexamscore.service.ISpartaSubjectService;
import com.yuda.afterexamscore.vo.TsinghuaFormalAnswerVo;
import com.yuda.common.core.web.domain.AjaxResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.nio.charset.Charset;
import java.util.*;
import java.util.concurrent.*;
import java.util.regex.Pattern;

/**
 * 南昊导入Controller
 *
 * @author fmx
 * @date 2021-05-31
 */
@RestController
@RequestMapping("/importScoreExcel")
@Api("excel表入库Controller")
@RefreshScope
public class ImportScoreExcelController {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Value("${examUUID}")
    private String examUUID;

    @Autowired
    private ISpartaSubjectService spartaSubjectService;

    @Autowired
    private IImportScoreExcelService importScoreExcelService;


    @Autowired
    private RedisTemplate redisTemplate;

    private static Logger log = LoggerFactory.getLogger(ImportScoreExcelController.class);

    private static Float process = 0f;

    private static String quesIndexs;

    private static Queue<Map<String, Object>> importScoreExcelQueue = new ConcurrentLinkedQueue<>();

    private static SpartaSubject spartaSubject;

    private static Map<String, Object> quesIndexMaps = new ConcurrentHashMap<>();

    private static Map<String, Object> quesIndexMaps2 = new ConcurrentHashMap<>();

    private static Map<String, Object> quesIndexMaps3 = new ConcurrentHashMap<>();

    private static Map<String, Object> NanHaoBySubject = new ConcurrentHashMap<>();

    //    @PostMapping
    //todo 2021年7月5日修改
    @PostMapping("/xiaozhao/{subjectRelationId}")
    @ApiOperation("校招导入,需要传入subjectRelationId")
    public AjaxResult exportScoreExcel(MultipartFile file, @PathVariable("subjectRelationId") String subjectRelationId) throws Exception {
        importScoreExcelQueue.clear();
        //现在tsinghua_formal_answer有主观题，是直接修改主观题还是插入另一个表，需要考虑
        //610只考虑某一科的主观题
        //生成dp_ques_score_nanhao插入表
        jdbcTemplate.execute(createSql);
        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
        List<Map<String, Object>> maps = reader.readAll();
        //创建一个stringbuilder接受所有数字
        StringBuilder stringBuilder = new StringBuilder();
        //获取表头
        List<Object> objects = reader.readRow(0);
        for (Object object : objects) {
            //判断是否为题号
            boolean numeric = isNumeric(object.toString());
            if (numeric) {
                stringBuilder.append(object.toString() + "#");
            }
        }
        quesIndexs = stringBuilder.toString();
        quesIndexs = quesIndexs.substring(0, quesIndexs.length() - 1);
        //获取各种类型subjectId
        List<SpartaSubject> query = jdbcTemplate.query("SELECT id,relation_subject_id,mapping_subject_id from sparta_subject where relation_subject_id = ?", new Object[]{subjectRelationId}, new BeanPropertyRowMapper<>(SpartaSubject.class));
        if (CollectionUtil.isNotEmpty(query)) {
            spartaSubject = query.get(0);
        } else {
            return new AjaxResult(304, "科目表有误");
        }
        importScoreExcelQueue.addAll(maps);
        //取一个来获取表头
        //我们最后只需要 zkz_num subject_relation_id ques_id ques_index score 即可
        //开线程，多线程处理分数
        int threadNum = (int) Math.ceil(maps.size() / 100) + 1;
        if (threadNum >= 1) {
            CountDownLatch latch = new CountDownLatch(threadNum);
            ExecutorService executor = Executors.newFixedThreadPool(100);
            if (threadNum > 0) {
                for (int i = 0; i < threadNum; i++) { // 开threadNum个线程
                    Runnable task = new ImportScoreExcelController.MultithreadingOfWorkData(latch);
                    // 执行
                    executor.execute(task);
                }
            }
            latch.await(); //
            executor.shutdown();//关闭线程池
        }
        //查看表里是否有重复数据
        List<TsinghuaFormalAnswerVo> errNums = jdbcTemplate.query("SELECT zkz_num,subject_relation_id ,ques_index,count(1) count from dp_ques_score_excel_nanhao GROUP BY zkz_num ,subject_relation_id,ques_index having count > 1", new BeanPropertyRowMapper<>(TsinghuaFormalAnswerVo.class));
        if (CollectionUtil.isNotEmpty(errNums)) {
            return new AjaxResult(304, "有重复数据,请查看");
        }
        //TODO 更新表里其他数据
        System.out.println("更新信息中");
        try {
            jdbcTemplate.batchUpdate("UPDATE dp_ques_score_excel_nanhao a ,sparta_exam_student b,sparta_subject c,dp_ques_index_mapping e\n" +
                    "set a.sfz_num = b.sfz_num,a.student_id = b.student_id,a.student_type = 1,a.pg_type = b.student_type,a.gaige = CASE\n" +
                    "WHEN b.student_type>=65 THEN\n" +
                    "0\n" +
                    "ELSE\n" +
                    "1\n" +
                    "END,a.ques_id = e.question_id,a.full_score = e.full_score,a.type = e.question_type \n" +
                    "where a.zkz_num = b.zkz_num and b.e_status =0 and b.student_id !='000000'\n" +
                    "and a.subject_relation_id = c.relation_subject_id and c.relation_subject_id not like '%#%'\n" +
                    "and a.subject_relation_id = e.subject_relation_id and a.ques_index = e.ques_index");
        } catch (Exception e) {
            e.printStackTrace();
            return new AjaxResult(304, "报错了，去查看日志");
        }
        return AjaxResult.success();
    }


    @PostMapping("/biaokao/exportZip")
    @ApiOperation("标考导入,需要传入考试试卷试题类型,1.全国一卷 2.全国二卷,3.改革区")
    public AjaxResult exportScoreZipBK(MultipartFile file) throws Exception {
        String outputPath = "D:/upload";
        BufferedInputStream bis = new BufferedInputStream(file.getInputStream());
        String filename = file.getOriginalFilename();
        byte bytes[] = new byte[1024];

        // 临时存放的zip的文件夹
        String tempPath = "D:\\upload\\";
        File tempFile = new File(tempPath + filename);

        if (!tempFile.getParentFile().exists()) {
            tempFile.getParentFile().mkdirs();
        }

        if (!tempFile.exists()) {
            tempFile.createNewFile();
        }
        BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(tempFile));
        int length = -1;
        while ((length = bis.read(bytes)) != -1) {
            bos.write(bytes, 0, length);
        }
        bos.close();
        bis.close();
        System.err.println("开始执行....");
        long startTime = System.currentTimeMillis();

        //生成dp_ques_score_excel_nanhao  dp_ques_score
        // 清空dp_ques_score dp_ques_score_nanhao
        jdbcTemplate.execute(createSql);
        jdbcTemplate.execute(createDpQuesSql);
        jdbcTemplate.execute("truncate table dp_ques_score");
        jdbcTemplate.execute("truncate table dp_ques_score_excel_nanhao ");

        // 解压之后包含3个文件夹
        File zipFilePath = ZipUtil.unzip(tempFile, new File(outputPath), Charset.forName("GBK"));
        File sourceFile = new File(zipFilePath, "source");
        if (sourceFile.isDirectory()) {
            File[] files = sourceFile.listFiles();
            for (File typeFile : files) {
                if ("一卷区处理_正式".equals(typeFile.getName())) {
                    int gaige = 0;
                    Set<Map<String, String>> distinctSubjectIds = new HashSet<>();
                    File listFile = new File(typeFile.getAbsolutePath() + File.separator + "小题分");
                    if (listFile.exists()) {
                        File[] fileScore = listFile.listFiles();
                        for (File scoreList : fileScore) {
                            Map<String, String> subjectMap = new HashMap<>();
                            String str = scoreList.getName();
                            // 只有数学是不一样,其他是一样的 文数 理数
                            String subjectType = str.substring(2, 4);
                            // 根据科目查询 subjectId
                            List<String> id = jdbcTemplate.queryForList("select id from sparta_subject where `name` = ? and id <=1010", String.class, subjectType);
                            if (CollectionUtil.isNotEmpty(id)) {
                                String subjectRelationId = id.get(0);
                                String getMappingSubjectId = "select mapping_subject_id from sparta_subject where relation_subject_id =  '" + subjectRelationId + "'";
                                String mappingSubjectId = jdbcTemplate.queryForObject(getMappingSubjectId, String.class);

                                String getSubjectIdSQL = "select DISTINCT subject_id from dp_ques_index_mapping  where subject_relation_id = '" + mappingSubjectId + "'";
                                String subjectId = jdbcTemplate.queryForObject(getSubjectIdSQL, String.class); // 1012  1010  1010
                                subjectMap.put("subjectId", subjectId);
                                subjectMap.put("subjectRelationId", subjectRelationId);
                                subjectMap.put("mappingSubjectId", mappingSubjectId);
                                distinctSubjectIds.add(subjectMap);
                                // 传科目id 和对应的文件给后台

                                NanHaoBySubject.clear();
                                NanHaoBySubject.put("subjectId", subjectId);
                                NanHaoBySubject.put("subjectRelationId", subjectRelationId);
                                NanHaoBySubject.put("mappingSubjectId", mappingSubjectId);
                                exportZipFile(scoreList, quesIndexMaps);
                            } else {
                                return new AjaxResult(304, "科目表有误");
                            }
                        }
                    }
                    // 删除表中的多选题多余的,只剩下一条有分的
                    jdbcTemplate.update("delete from dp_ques_score where score = ''");
                    for (Map<String, String> subjectMapObj : distinctSubjectIds) {
                        // 这里面的已经被去重了  语文和英语不用保存2次 此时改变算分状态
                        redisTemplate.opsForHash().put(examUUID + "ProcessOf" + subjectMapObj.get("subjectRelationId"), "quesScore", "1");
                        importScoreExcelService.batchInsertIntoScore(examUUID, subjectMapObj, gaige);
                    }
                }
                if ("二卷区处理_正式".equals(typeFile.getName())) {
                    int gaige = 0;
                    Set<Map<String, String>> distinctSubjectIds = new HashSet<>();
                    File listFile = new File(typeFile.getAbsolutePath() + File.separator + "小题分");
                    if (listFile.exists()) {
                        File[] fileScore = listFile.listFiles();
                        for (File scoreList : fileScore) {
                            Map<String, String> subjectMap = new HashMap<>();
                            String str = scoreList.getName();
                            // 只有数学是不一样,其他是一样的 文数 理数
                            String subjectType = str.substring(2, 4);
                            // 根据科目查询 subjectId
                            List<String> id = jdbcTemplate.queryForList("select id from sparta_subject where `name` = ? and id between 2001 and 2010", String.class, subjectType);
                            if (CollectionUtil.isNotEmpty(id)) {
                                String subjectRelationId = id.get(0);
                                String getMappingSubjectId = "select mapping_subject_id from sparta_subject where relation_subject_id =  '" + subjectRelationId + "'";
                                String mappingSubjectId = jdbcTemplate.queryForObject(getMappingSubjectId, String.class);

                                String getSubjectIdSQL = "select DISTINCT subject_id from dp_ques_index_mapping  where subject_relation_id = '" + mappingSubjectId + "'";
                                String subjectId = jdbcTemplate.queryForObject(getSubjectIdSQL, String.class); // 1012  1010  1010

                                subjectMap.put("subjectId", subjectId);
                                subjectMap.put("subjectRelationId", subjectRelationId);
                                subjectMap.put("mappingSubjectId", mappingSubjectId);
                                distinctSubjectIds.add(subjectMap);

                                // 传科目id 和对应的文件给后台
                                NanHaoBySubject.clear();
                                NanHaoBySubject.put("subjectId", subjectId);
                                NanHaoBySubject.put("subjectRelationId", subjectRelationId);
                                NanHaoBySubject.put("mappingSubjectId", mappingSubjectId);
                                exportZipFile(scoreList, quesIndexMaps2);
                            }
                        }
                    }
                    System.err.println(distinctSubjectIds);
                    System.out.println(quesIndexMaps2);
                    // 删除表中的多选题多余的,只剩下一条有分的
                    jdbcTemplate.update("delete from dp_ques_score where score = ''");
                    for (Map<String, String> subjectMapObj : distinctSubjectIds) {
                        // 这里面的已经被去重了  语文和英语不用保存2次 此时改变算分状态
                        redisTemplate.opsForHash().put(examUUID + "ProcessOf" + subjectMapObj.get("subjectRelationId"), "quesScore", "1");
                        importScoreExcelService.batchInsertIntoScore(examUUID, subjectMapObj, gaige);
                    }
                }
                if ("改革区处理_正式".equals(typeFile.getName())) {
                    int gaige = 1;
                    Set<Map<String, String>> distinctSubjectIds = new HashSet<>();
                    File listFile = new File(typeFile.getAbsolutePath() + File.separator + "小题分");
                    if (listFile.exists()) {
                        File[] fileScore = listFile.listFiles();
                        for (File scoreList : fileScore) {
                            Map<String, String> subjectMap = new HashMap<>();
                            String str = scoreList.getName();
                            // 只有数学是不一样,其他是一样的 文数 理数
                            String subjectType = str.substring(2, 4);
                            // 根据科目查询 subjectId
                            List<String> id = jdbcTemplate.queryForList("select id from sparta_subject where `name` = ? and id between 3001 and 3010", String.class, subjectType);
                            if (CollectionUtil.isNotEmpty(id)) {
                                String subjectRelationId = id.get(0);
                                String getMappingSubjectId = "select mapping_subject_id from sparta_subject where relation_subject_id =  '" + subjectRelationId + "'";
                                String mappingSubjectId = jdbcTemplate.queryForObject(getMappingSubjectId, String.class);

                                String getSubjectIdSQL = "select DISTINCT subject_id from dp_ques_index_mapping  where subject_relation_id = '" + mappingSubjectId + "'";
                                String subjectId = jdbcTemplate.queryForObject(getSubjectIdSQL, String.class); // 1012  1010  1010
                                subjectMap.put("subjectId", subjectId);
                                subjectMap.put("subjectRelationId", subjectRelationId);
                                subjectMap.put("mappingSubjectId", mappingSubjectId);
                                distinctSubjectIds.add(subjectMap);
                                // 传科目id 和对应的文件给后台

                                NanHaoBySubject.clear();
                                NanHaoBySubject.put("subjectId", subjectId);
                                NanHaoBySubject.put("subjectRelationId", subjectRelationId);
                                NanHaoBySubject.put("mappingSubjectId", mappingSubjectId);
                                exportZipFile(scoreList, quesIndexMaps3);
                            }
                        }
                    }
                    System.err.println(distinctSubjectIds);
                    System.out.println(quesIndexMaps3);
                    // 删除表中的多选题多余的,只剩下一条有分的
                    jdbcTemplate.update("delete from dp_ques_score where score = ''");
                    for (Map<String, String> subjectMapObj : distinctSubjectIds) {
                        // 这里面的已经被去重了  语文和英语不用保存2次 此时改变算分状态
                        redisTemplate.opsForHash().put(examUUID + "ProcessOf" + subjectMapObj.get("subjectRelationId"), "quesScore", "1");
                        importScoreExcelService.batchInsertIntoScore(examUUID, subjectMapObj, gaige);
                    }
                }
            }
        } else {
            return new AjaxResult(304, "zip压缩包格式有问题");
        }
        System.err.println("一共耗时" + ((System.currentTimeMillis() - startTime) / 1000) + "秒");
        return AjaxResult.success();
    }


    public AjaxResult exportZipFile(File file, Map<String, Object> quesIndexMaps) throws Exception {
        // 每次清空题目
        quesIndexMaps.clear();
        importScoreExcelQueue.clear();

        ExcelReader reader = ExcelUtil.getReader(new FileInputStream(file));
        List<Map<String, Object>> maps = reader.readAll();
        //获取表头
        List<Object> objects = reader.readRow(0);
        for (Object object : objects) {
            //判断是否为题号
            String str = object.toString();
            boolean numeric = isNumeric(str);
            if (numeric) {
                quesIndexMaps.put(str, str);
            }
            if (str.contains("-") && str.contains("k")) {
                quesIndexMaps.put(str, str.substring(str.indexOf("-") + 1));
            }
            if (str.contains("-") && str.contains("(") && str.contains(")")) {
                quesIndexMaps.put(str, str.substring(str.indexOf("(") + 1, str.indexOf(")")));
            }
        }
        importScoreExcelQueue.addAll(maps);
        //取一个来获取表头
        //我们最后只需要 zkz_num subject_relation_id ques_id ques_index score 即可
        //开线程，多线程处理分数
        //int threadNum = (int) Math.ceil(maps.size() / 1000) + 1;
        //int threadNum = Runtime.getRuntime().availableProcessors();
        int threadNum = 1;

        CountDownLatch latch = new CountDownLatch(threadNum);
        ExecutorService executor = Executors.newFixedThreadPool(threadNum);
        for (int i = 0; i < threadNum; i++) { // 开threadNum个线程
            // 执行
            executor.execute(new ImportScoreExcelController.MultithreadingOfAllData(latch, quesIndexMaps));
        }
        latch.await(); //
        executor.shutdown();//关闭线程池
//        //查看表里是否有重复数据
//        List<TsinghuaFormalAnswerVo> errNums = jdbcTemplate.query("SELECT zkz_num,subject_relation_id ,ques_index,count(1) count from dp_ques_score_excel_nanhao GROUP BY zkz_num ,subject_relation_id,ques_index having count > 1", new BeanPropertyRowMapper<>(TsinghuaFormalAnswerVo.class));
//        if (CollectionUtil.isNotEmpty(errNums)) {
//            return new AjaxResult(304, "有重复数据,请查看");
//        }
        //TODO 更新表里其他数据
        System.out.println("更新信息中");

        return AjaxResult.success();
    }


    @PostMapping("/biaokao/{subjectRelationId}")
    @ApiOperation("标考导入,需要传入subjectRelationId")
    public AjaxResult exportScoreExcelBK(MultipartFile file, @PathVariable("subjectRelationId") String subjectRelationId) throws Exception {
        //先删除
        jdbcTemplate.update("delete from dp_ques_score where subject_relation_id = ? ", new Object[]{subjectRelationId});
        jdbcTemplate.update("delete from dp_ques_score_excel_nanhao where subject_relation_id = ? ", new Object[]{subjectRelationId});
        importScoreExcelQueue.clear();
        //生成dp_ques_score_nanhao插入表
        jdbcTemplate.execute(createSql);
        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
        List<Map<String, Object>> maps = reader.readAll();
        //创建一个stringbuilder接受所有数字
        StringBuilder stringBuilder = new StringBuilder();
        //获取表头
        List<Object> objects = reader.readRow(0);
        for (Object object : objects) {
            //判断是否为题号
            String str = object.toString();
            boolean numeric = isNumeric(str);
            if (numeric) {
                stringBuilder.append(str + "#");
            }
            if (str.contains("-") && str.contains("k")) {
                stringBuilder.append(str.substring(str.indexOf("-") + 1) + "#");
            }
            if (str.contains("-") && str.contains("(") && str.contains(")")) {
                stringBuilder.append(str.substring(str.indexOf("(") + 1, str.indexOf(")")) + "#");
            }
        }
        quesIndexs = stringBuilder.toString();
        quesIndexs = quesIndexs.substring(0, quesIndexs.length() - 1);
        //获取各种类型subjectId
        List<SpartaSubject> query = jdbcTemplate.query("SELECT id,relation_subject_id,mapping_subject_id from sparta_subject where relation_subject_id = ?", new Object[]{subjectRelationId}, new BeanPropertyRowMapper<>(SpartaSubject.class));
        if (CollectionUtil.isNotEmpty(query)) {
            spartaSubject = query.get(0);
        } else {
            return new AjaxResult(304, "科目表有误");
        }
        importScoreExcelQueue.addAll(maps);
        //取一个来获取表头
        //我们最后只需要 zkz_num subject_relation_id ques_id ques_index score 即可
        //开线程，多线程处理分数
        int threadNum = (int) Math.ceil(maps.size() / 100) + 1;
        if (threadNum >= 1) {
            CountDownLatch latch = new CountDownLatch(threadNum);
            ExecutorService executor = Executors.newFixedThreadPool(100);
            if (threadNum > 0) {
                for (int i = 0; i < threadNum; i++) { // 开threadNum个线程
                    Runnable task = new ImportScoreExcelController.MultithreadingOfWorkData(latch);
                    // 执行
                    executor.execute(task);
                }
            }
            latch.await(); //
            executor.shutdown();//关闭线程池
        }
        //查看表里是否有重复数据
        List<TsinghuaFormalAnswerVo> errNums = jdbcTemplate.query("SELECT zkz_num,subject_relation_id ,ques_index,count(1) count from dp_ques_score_excel_nanhao GROUP BY zkz_num ,subject_relation_id,ques_index having count > 1", new BeanPropertyRowMapper<>(TsinghuaFormalAnswerVo.class));
        if (CollectionUtil.isNotEmpty(errNums)) {
            return new AjaxResult(304, "有重复数据,请查看");
        }
        //TODO 更新表里其他数据
        System.out.println("更新信息中");
        try {
            jdbcTemplate.batchUpdate("UPDATE dp_ques_score_excel_nanhao a ,sparta_exam_student b,sparta_subject c,dp_ques_index_mapping e\n" +
                    "set a.sfz_num = b.sfz_num,a.student_id = b.student_id,a.student_type = 1,a.pg_type = b.student_type,a.gaige = CASE\n" +
                    "WHEN b.student_type>=65 THEN\n" +
                    "0\n" +
                    "ELSE\n" +
                    "1\n" +
                    "END,a.ques_id = e.question_id,a.full_score = e.full_score,a.type = e.question_type,a.province_id = b.province_id,a.city_id = b.city_id,a.school_id = b.school_id\n" +
                    "where a.zkz_num = b.zkz_num and b.e_status =0 and b.student_id !='000000'\n" +
                    "and a.subject_relation_id = c.relation_subject_id and c.relation_subject_id not like '%#%'\n" +
                    "and a.subject_relation_id = e.subject_relation_id and a.ques_index = e.ques_index");
        } catch (Exception e) {
            e.printStackTrace();
            return new AjaxResult(304, "报错了，去查看日志");
        }
        return AjaxResult.success();
    }

    @PostMapping("/xiaozhao/subjectiveQuestionsInsert")
    @ApiOperation("将中间表的主观题插入真实dp_ques_score中，校招版本")
    public AjaxResult subjectiveQuestionsInsert() {
        jdbcTemplate.execute("insert into dp_ques_score (zkz_num,sfz_num,student_id,student_type,pg_type,gaige,province_id,city_id,school_id,banji_name,subject_id,subject_relation_id,mapping_subject_id,ques_id,ques_index,answer,score,type,answer2,full_score)\n" +
                "SELECT a.zkz_num,a.sfz_num,a.student_id,a.student_type,a.pg_type,a.gaige,a.province_id,a.city_id,a.school_id,a.banji_name,a.subject_id,a.subject_relation_id,a.mapping_subject_id,a.ques_id,a.ques_index,a.answer,a.score,a.type,a.answer2,a.full_score \n" +
                "from dp_ques_score_excel_nanhao a \n" +
                "LEFT JOIN dp_ques_score b on a.student_id = b.student_id and a.ques_id = b.ques_id and a.subject_relation_id = b.subject_relation_id\n" +
                "where b.student_id is null");
        return AjaxResult.success();
    }

//    @PostMapping("/biaokao/subjectiveQuestionsInsert")
//    @ApiOperation("将中间表的主观题插入真实dp_ques_score中,标考版本")
//    public AjaxResult subjectiveQuestionsInsertBiaoKao() {
//        //todo 2021年7月7日加入验证
//
//        // 添加之前先截断
//        jdbcTemplate.execute("truncate table dp_ques_score");
//        //验证南昊的数据是否和formal_answer表一样
//        jdbcTemplate.execute("insert into dp_ques_score (zkz_num,sfz_num,student_id,student_type,pg_type,gaige,province_id,city_id,school_id,banji_name,subject_id,subject_relation_id,mapping_subject_id,ques_id,ques_index,answer,score,type,answer2,full_score)\n" +
//                "SELECT a.zkz_num,a.sfz_num,a.student_id,a.student_type,a.pg_type,a.gaige,a.province_id,a.city_id,a.school_id,a.banji_name,a.subject_id,a.subject_relation_id,a.mapping_subject_id,a.ques_id,a.ques_index,a.answer,a.score,a.type,a.answer2,a.full_score \n" +
//                "from dp_ques_score_excel_nanhao a \n" +
//                "LEFT JOIN dp_ques_score b on a.student_id = b.student_id and a.ques_id = b.ques_id and a.subject_relation_id = b.subject_relation_id\n" +
//                "where b.student_id is null");
//
//        // 将dp_ques_score_excel_nahao表数据同步到dp_ques_score
//        List<String> subjectIds = jdbcTemplate.queryForList("SELECT DISTINCT subject_relation_id from dp_ques_score", String.class);
//        //改变状态
//        for (String subjectId : subjectIds) {
//            redisTemplate.opsForHash().put(examUUID + "ProcessOf" + subjectId, "quesScore", "1");
//        }
//        return AjaxResult.success();
//    }


    @PostMapping("/biaokao/subjectiveQuestionsInsert")
    @ApiOperation("将中间表的主观题插入真实dp_ques_score中,标考版本")
    public AjaxResult subjectiveQuestionsInsertBiaoKao() {
        //todo 2021年7月7日加入验证
        // 添加之前先截断
        jdbcTemplate.execute("truncate table dp_ques_score");
        //验证南昊的数据是否和formal_answer表一样
        jdbcTemplate.execute("insert into dp_ques_score (zkz_num,sfz_num,student_id,student_type,pg_type,gaige,province_id,city_id,school_id,banji_name,subject_id,subject_relation_id,mapping_subject_id,ques_id,ques_index,score,type,full_score)\n" +
                "SELECT a.zkz_num,a.sfz_num,a.student_id,a.student_type,a.pg_type,a.gaige,a.province_id,a.city_id,a.school_id,a.banji_name,a.subject_id,a.subject_relation_id,a.mapping_subject_id,a.ques_id,a.ques_index,a.score,a.type,a.full_score \n" +
                "from dp_ques_score_excel_nanhao a");

        // 将dp_ques_score_excel_nahao表数据同步到dp_ques_score
        List<String> subjectIds = jdbcTemplate.queryForList("SELECT DISTINCT subject_relation_id from dp_ques_score", String.class);
        //改变状态
        for (String subjectId : subjectIds) {
            redisTemplate.opsForHash().put(examUUID + "ProcessOf" + subjectId, "quesScore", "1");
        }
        // 在这个地方更新formal_answer
        return AjaxResult.success();
    }




    // 2021-11-23新增
    @PostMapping("/biaokao/subjectQuesSync")
    @ApiOperation("将中间表的主观题插入真实dp_ques_score中,标考版本")
    public AjaxResult subjectQuesSyncBiaoKao() {
        // 添加之前先截断
        jdbcTemplate.execute("truncate table dp_ques_score");

        //// 将dp_ques_score_excel_nahao表数据同步到dp_ques_score
        jdbcTemplate.execute("insert into dp_ques_score (zkz_num,sfz_num,student_id,student_type,pg_type,gaige,province_id,city_id,school_id,banji_name,subject_id,subject_relation_id,mapping_subject_id,ques_id,ques_index,score,type,full_score)\n" +
                "SELECT zkz_num,sfz_num,student_id,student_type,pg_type,gaige,province_id,city_id,school_id,banji_name,subject_id,subject_relation_id,mapping_subject_id,ques_id,ques_index,score,type,full_score \n" +
                "from dp_ques_score_excel_nanhao");
        //验证南昊的数据是否和formal_answer表一样
        List<String> subjectIds = jdbcTemplate.queryForList("SELECT DISTINCT subject_relation_id from dp_ques_score", String.class);
        //改变状态
        for (String subjectId : subjectIds) {
            redisTemplate.opsForHash().put(examUUID + "ProcessOf" + subjectId, "quesScore", "1");
        }
        return AjaxResult.success();
    }


//---------------------------------------------------分割线--------------------------------------------------------------------------------------------------


    /**
     * 多线程之填空插入穷举表资源类
     */
    private class MultithreadingOfWorkData implements Runnable {
        private CountDownLatch threadsSignal;

        public MultithreadingOfWorkData(CountDownLatch threadsSignal) {
            this.threadsSignal = threadsSignal;
        }

        @Override
        public void run() {
            List<Object[]> objects = new CopyOnWriteArrayList<>();
            while (!importScoreExcelQueue.isEmpty()) {
                Map<String, Object> vo = importScoreExcelQueue.poll();//消费
                //取出各题分数塞进新的队列
                //最后只需要 zkz_num subject_relation_id ques_id ques_index score 即可
                Object zkzNum = vo.get("考号");
                for (Map.Entry<String, Object> entry : vo.entrySet()) {
                    if (quesIndexs.contains(entry.getKey())) {
                        Object value = entry.getValue();
                        String score = value.toString();
                        if (StrUtil.isBlank(score)) {
                            score = "0";
                        }
                        objects.add(new Object[]{zkzNum, spartaSubject.getId(), spartaSubject.getRelationSubjectId(), spartaSubject.getMappingSubjectId(), entry.getKey(), score});
                    }
                }
            }
            log.info("开始线程：" + threadsSignal.getCount());
            jdbcTemplate.batchUpdate(insertSql, objects);
            // 线程结束时计数器减1
            threadsSignal.countDown();//必须等核心处理逻辑处理完成后才可以减1
            log.info(Thread.currentThread().getName() + "结束. 还有" + threadsSignal.getCount() + " 个线程 ");
        }

    }


    /**
     * 多线程之插入穷举表资源类
     */
    private class MultithreadingOfAllData implements Runnable {
        private CountDownLatch threadsSignal;
        private Map<String, Object> quesIndexMaps;


        public MultithreadingOfAllData(CountDownLatch threadsSignal, Map<String, Object> quesIndexMaps) {
            this.threadsSignal = threadsSignal;
            this.quesIndexMaps = quesIndexMaps;
        }

        @Override
        public void run() {
            List<Map<String, Object>> listMap = new ArrayList<>();
            while (!importScoreExcelQueue.isEmpty()) {
                Map<String, Object> vo = importScoreExcelQueue.poll();//消费
                //取出各题分数塞进新的队列
                //最后只需要 zkz_num subject_relation_id ques_id ques_index score 即可
                Object zkzNum = vo.get("考号");
                // 2021-10-29修改
                Set<String> strings = quesIndexMaps.keySet();
                for (String scoreKey : strings) {
                    String score = vo.get(scoreKey) + "";
                    Map<String, Object> map = new HashMap<>();
                    map.put("zkzNum", zkzNum);
                    map.put("subjectId", NanHaoBySubject.get("subjectId"));
                    map.put("relationSubjectId", NanHaoBySubject.get("subjectRelationId"));
                    map.put("mappingSubjectId", NanHaoBySubject.get("mappingSubjectId"));
                    map.put("quesIndex", quesIndexMaps.get(scoreKey));
                    map.put("score", score);
                    listMap.add(map);
                }
            }
            log.info("开始线程：" + threadsSignal.getCount());
            if (CollectionUtil.isNotEmpty(listMap)) {
                importScoreExcelService.insertScoreBatch(listMap);
            }
            // 线程结束时计数器减1
            threadsSignal.countDown();//必须等核心处理逻辑处理完成后才可以减1
            log.info(Thread.currentThread().getName() + "结束. 还有" + threadsSignal.getCount() + " 个线程 ");
        }

    }


    //判断是否为数字
    public static boolean isNumeric(String str) {
        Pattern pattern = Pattern.compile("[0-9]*");
        return pattern.matcher(str).matches();
    }

    //插入语句
    private static final String insertSql = "INSERT into dp_ques_score_excel_nanhao (zkz_num,subject_id,subject_relation_id,mapping_subject_id,ques_index,score) VALUES (?,?,?,?,?,?)";

    //建表语句
    private static final String createSql = "CREATE TABLE IF NOT EXISTS `dp_ques_score_excel_nanhao` (\n" +
            "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
            "  `zkz_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '准考证(12)位',\n" +
            "  `sfz_num` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '身份证',\n" +
            "  `student_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生ID',\n" +
            "  `student_type` tinyint(4) DEFAULT NULL COMMENT '1线上  2线下',\n" +
            "  `pg_type` tinyint(4) DEFAULT NULL COMMENT 'pg_id',\n" +
            "  `gaige` tinyint(1) DEFAULT NULL,\n" +
            "  `province_id` int(11) DEFAULT NULL COMMENT '省ID',\n" +
            "  `city_id` int(11) DEFAULT NULL,\n" +
            "  `school_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学校ID',\n" +
            "  `banji_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '班级名称',\n" +
            "  `subject_id` int(11) DEFAULT NULL COMMENT '大科目ID',\n" +
            "  `subject_relation_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '小科目ID(共17科单科，6科总科)',\n" +
            "  `mapping_subject_id` int(11) DEFAULT NULL,\n" +
            "  `ques_id` int(11) DEFAULT NULL COMMENT '题目ID',\n" +
            "  `ques_index` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '题目序号',\n" +
            "  `answer` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '答案',\n" +
            "  `score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分数',\n" +
            "  `type` tinyint(4) DEFAULT NULL COMMENT '1 一卷   2 二卷',\n" +
            "  `sum_score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `full_score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `answer2` text CHARACTER SET utf8 COLLATE utf8_general_ci,\n" +
            "  PRIMARY KEY (`id`) USING BTREE,\n" +
            "  KEY `zkz_num` (`zkz_num`) USING BTREE,\n" +
            "  KEY `ques_index` (`ques_index`) USING BTREE,\n" +
            "  KEY `ques_id` (`ques_id`) USING BTREE,\n" +
            "  KEY `student_id` (`student_id`) USING BTREE,\n" +
            "  KEY `student_id_4` (`student_id`,`subject_id`) USING BTREE,\n" +
            "  KEY `score` (`score`) USING BTREE,\n" +
            "  KEY `mapping_subject_id` (`mapping_subject_id`) USING BTREE,\n" +
            "  KEY `full_score` (`full_score`) USING BTREE,\n" +
            "  KEY `gaige` (`gaige`) USING BTREE,\n" +
            "  KEY `student_type` (`student_type`) USING BTREE,\n" +
            "  KEY `zkz_num_2` (`zkz_num`,`mapping_subject_id`) USING BTREE,\n" +
            "  KEY `student_id_5` (`student_id`,`ques_id`,`score`) USING BTREE,\n" +
            "  KEY `student_id_6` (`student_id`,`ques_id`) USING BTREE,\n" +
            "  KEY `sfz_num` (`sfz_num`) USING BTREE\n" +
            ") ENGINE=InnoDB AUTO_INCREMENT=106491 DEFAULT CHARSET=utf8 COMMENT='单题分数表（如每科有30道题目的话，那么5万个学生应该有30*5*17=2500万条数据)';";


    //建表语句
    private static final String createDpQuesSql = "CREATE TABLE IF NOT EXISTS `dp_ques_score` (\n" +
            "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
            "  `zkz_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '准考证(12)位',\n" +
            "  `sfz_num` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '身份证',\n" +
            "  `student_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生ID',\n" +
            "  `student_type` tinyint(4) DEFAULT NULL COMMENT '1线上  2线下',\n" +
            "  `pg_type` tinyint(4) DEFAULT NULL COMMENT 'pg_id',\n" +
            "  `gaige` tinyint(1) DEFAULT NULL,\n" +
            "  `province_id` int(11) DEFAULT NULL COMMENT '省ID',\n" +
            "  `city_id` int(11) DEFAULT NULL,\n" +
            "  `school_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学校ID',\n" +
            "  `banji_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '班级名称',\n" +
            "  `subject_id` int(11) DEFAULT NULL COMMENT '大科目ID',\n" +
            "  `subject_relation_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '小科目ID(共17科单科，6科总科)',\n" +
            "  `mapping_subject_id` int(11) DEFAULT NULL,\n" +
            "  `ques_id` int(11) DEFAULT NULL COMMENT '题目ID',\n" +
            "  `ques_index` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '题目序号',\n" +
            "  `answer` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '答案',\n" +
            "  `score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分数',\n" +
            "  `type` tinyint(4) DEFAULT NULL COMMENT '1 一卷   2 二卷',\n" +
            "  `sum_score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `full_score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `answer2` text CHARACTER SET utf8 COLLATE utf8_general_ci,\n" +
            "  PRIMARY KEY (`id`) USING BTREE,\n" +
            "  KEY `zkz_num` (`zkz_num`) USING BTREE,\n" +
            "  KEY `ques_index` (`ques_index`) USING BTREE,\n" +
            "  KEY `ques_id` (`ques_id`) USING BTREE,\n" +
            "  KEY `student_id` (`student_id`) USING BTREE,\n" +
            "  KEY `student_id_4` (`student_id`,`subject_id`) USING BTREE,\n" +
            "  KEY `score` (`score`) USING BTREE,\n" +
            "  KEY `mapping_subject_id` (`mapping_subject_id`) USING BTREE,\n" +
            "  KEY `full_score` (`full_score`) USING BTREE,\n" +
            "  KEY `gaige` (`gaige`) USING BTREE,\n" +
            "  KEY `student_type` (`student_type`) USING BTREE,\n" +
            "  KEY `zkz_num_2` (`zkz_num`,`mapping_subject_id`) USING BTREE,\n" +
            "  KEY `student_id_5` (`student_id`,`ques_id`,`score`) USING BTREE,\n" +
            "  KEY `student_id_6` (`student_id`,`ques_id`) USING BTREE,\n" +
            "  KEY `sfz_num` (`sfz_num`) USING BTREE\n" +
            ") ENGINE=InnoDB AUTO_INCREMENT=106491 DEFAULT CHARSET=utf8 COMMENT='单题分数表（如每科有30道题目的话，那么5万个学生应该有30*5*17=2500万条数据)';";
}
